{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Handling duplicate rows and rows with missing values\n",
    "\n",
    "Most machine learning algorithms will return an error if they encounter a missing value.  So, you often have to remove rows with missing values from your DataFrame.\n",
    "\n",
    "To learn how, we need to create a pandas DataFrame and load it with data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The flight delays data set contains information about flights and flight delays"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>FL_DATE</th>\n",
       "      <th>OP_UNIQUE_CARRIER</th>\n",
       "      <th>TAIL_NUM</th>\n",
       "      <th>OP_CARRIER_FL_NUM</th>\n",
       "      <th>ORIGIN</th>\n",
       "      <th>DEST</th>\n",
       "      <th>CRS_DEP_TIME</th>\n",
       "      <th>DEP_TIME</th>\n",
       "      <th>DEP_DELAY</th>\n",
       "      <th>CRS_ARR_TIME</th>\n",
       "      <th>ARR_TIME</th>\n",
       "      <th>ARR_DELAY</th>\n",
       "      <th>CRS_ELAPSED_TIME</th>\n",
       "      <th>ACTUAL_ELAPSED_TIME</th>\n",
       "      <th>AIR_TIME</th>\n",
       "      <th>DISTANCE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>WN</td>\n",
       "      <td>N221WN</td>\n",
       "      <td>802</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>BWI</td>\n",
       "      <td>905</td>\n",
       "      <td>903.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>1450</td>\n",
       "      <td>1433.0</td>\n",
       "      <td>-17.0</td>\n",
       "      <td>225</td>\n",
       "      <td>210.0</td>\n",
       "      <td>197.0</td>\n",
       "      <td>1670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>WN</td>\n",
       "      <td>N8329B</td>\n",
       "      <td>3744</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>BWI</td>\n",
       "      <td>1500</td>\n",
       "      <td>1458.0</td>\n",
       "      <td>-2.0</td>\n",
       "      <td>2045</td>\n",
       "      <td>2020.0</td>\n",
       "      <td>-25.0</td>\n",
       "      <td>225</td>\n",
       "      <td>202.0</td>\n",
       "      <td>191.0</td>\n",
       "      <td>1670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>WN</td>\n",
       "      <td>N920WN</td>\n",
       "      <td>1019</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>DAL</td>\n",
       "      <td>1800</td>\n",
       "      <td>1802.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2045</td>\n",
       "      <td>2032.0</td>\n",
       "      <td>-13.0</td>\n",
       "      <td>105</td>\n",
       "      <td>90.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>WN</td>\n",
       "      <td>N480WN</td>\n",
       "      <td>1499</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>DAL</td>\n",
       "      <td>950</td>\n",
       "      <td>947.0</td>\n",
       "      <td>-3.0</td>\n",
       "      <td>1235</td>\n",
       "      <td>1223.0</td>\n",
       "      <td>-12.0</td>\n",
       "      <td>105</td>\n",
       "      <td>96.0</td>\n",
       "      <td>81.0</td>\n",
       "      <td>580</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>WN</td>\n",
       "      <td>N227WN</td>\n",
       "      <td>3635</td>\n",
       "      <td>ABQ</td>\n",
       "      <td>DAL</td>\n",
       "      <td>1150</td>\n",
       "      <td>1151.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1430</td>\n",
       "      <td>1423.0</td>\n",
       "      <td>-7.0</td>\n",
       "      <td>100</td>\n",
       "      <td>92.0</td>\n",
       "      <td>80.0</td>\n",
       "      <td>580</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      FL_DATE OP_UNIQUE_CARRIER TAIL_NUM  OP_CARRIER_FL_NUM ORIGIN DEST  \\\n",
       "0  2018-10-01                WN   N221WN                802    ABQ  BWI   \n",
       "1  2018-10-01                WN   N8329B               3744    ABQ  BWI   \n",
       "2  2018-10-01                WN   N920WN               1019    ABQ  DAL   \n",
       "3  2018-10-01                WN   N480WN               1499    ABQ  DAL   \n",
       "4  2018-10-01                WN   N227WN               3635    ABQ  DAL   \n",
       "\n",
       "   CRS_DEP_TIME  DEP_TIME  DEP_DELAY  CRS_ARR_TIME  ARR_TIME  ARR_DELAY  \\\n",
       "0           905     903.0       -2.0          1450    1433.0      -17.0   \n",
       "1          1500    1458.0       -2.0          2045    2020.0      -25.0   \n",
       "2          1800    1802.0        2.0          2045    2032.0      -13.0   \n",
       "3           950     947.0       -3.0          1235    1223.0      -12.0   \n",
       "4          1150    1151.0        1.0          1430    1423.0       -7.0   \n",
       "\n",
       "   CRS_ELAPSED_TIME  ACTUAL_ELAPSED_TIME  AIR_TIME  DISTANCE  \n",
       "0               225                210.0     197.0      1670  \n",
       "1               225                202.0     191.0      1670  \n",
       "2               105                 90.0      80.0       580  \n",
       "3               105                 96.0      81.0       580  \n",
       "4               100                 92.0      80.0       580  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "delays_df = pd.read_csv('Data/Lots_of_flight_data.csv')\n",
    "delays_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**info**  will tell us how many rows are in the DataFrame and for each column how many of those rows contain non-null values. From this we can determine which columns (if any) contain null/missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 300000 entries, 0 to 299999\n",
      "Data columns (total 16 columns):\n",
      "FL_DATE                300000 non-null object\n",
      "OP_UNIQUE_CARRIER      300000 non-null object\n",
      "TAIL_NUM               299660 non-null object\n",
      "OP_CARRIER_FL_NUM      300000 non-null int64\n",
      "ORIGIN                 300000 non-null object\n",
      "DEST                   300000 non-null object\n",
      "CRS_DEP_TIME           300000 non-null int64\n",
      "DEP_TIME               296825 non-null float64\n",
      "DEP_DELAY              296825 non-null float64\n",
      "CRS_ARR_TIME           300000 non-null int64\n",
      "ARR_TIME               296574 non-null float64\n",
      "ARR_DELAY              295832 non-null float64\n",
      "CRS_ELAPSED_TIME       300000 non-null int64\n",
      "ACTUAL_ELAPSED_TIME    295832 non-null float64\n",
      "AIR_TIME               295832 non-null float64\n",
      "DISTANCE               300000 non-null int64\n",
      "dtypes: float64(6), int64(5), object(5)\n",
      "memory usage: 30.9+ MB\n"
     ]
    }
   ],
   "source": [
    "delays_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "TAIL_NUM, DEP_TIME, DEP_DELAY, ARR_TIME, ARR_DELAY, ACTUAL_ELAPSED_TIME, and AIR_TIME all have rows with missing values."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are many techniques to deal with missing values, the simplest is to delete the rows with missing values.\n",
    "\n",
    "**dropna** will delete rows containing null/missing values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 295832 entries, 0 to 299999\n",
      "Data columns (total 16 columns):\n",
      "FL_DATE                295832 non-null object\n",
      "OP_UNIQUE_CARRIER      295832 non-null object\n",
      "TAIL_NUM               295832 non-null object\n",
      "OP_CARRIER_FL_NUM      295832 non-null int64\n",
      "ORIGIN                 295832 non-null object\n",
      "DEST                   295832 non-null object\n",
      "CRS_DEP_TIME           295832 non-null int64\n",
      "DEP_TIME               295832 non-null float64\n",
      "DEP_DELAY              295832 non-null float64\n",
      "CRS_ARR_TIME           295832 non-null int64\n",
      "ARR_TIME               295832 non-null float64\n",
      "ARR_DELAY              295832 non-null float64\n",
      "CRS_ELAPSED_TIME       295832 non-null int64\n",
      "ACTUAL_ELAPSED_TIME    295832 non-null float64\n",
      "AIR_TIME               295832 non-null float64\n",
      "DISTANCE               295832 non-null int64\n",
      "dtypes: float64(6), int64(5), object(5)\n",
      "memory usage: 32.7+ MB\n"
     ]
    }
   ],
   "source": [
    "delay_no_nulls_df = delays_df.dropna()   # Delete the rows with missing values\n",
    "delay_no_nulls_df.info()                 # Check the number of rows and number of rows with non-null values to confirm"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If you don't need to keep the original DataFrame, you can just delete the rows within the existing DataFrame instead of creating a new one\n",
    "\n",
    "**inplace=*True*** indicates you want to drop the rows in the specified DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 295832 entries, 0 to 299999\n",
      "Data columns (total 16 columns):\n",
      "FL_DATE                295832 non-null object\n",
      "OP_UNIQUE_CARRIER      295832 non-null object\n",
      "TAIL_NUM               295832 non-null object\n",
      "OP_CARRIER_FL_NUM      295832 non-null int64\n",
      "ORIGIN                 295832 non-null object\n",
      "DEST                   295832 non-null object\n",
      "CRS_DEP_TIME           295832 non-null int64\n",
      "DEP_TIME               295832 non-null float64\n",
      "DEP_DELAY              295832 non-null float64\n",
      "CRS_ARR_TIME           295832 non-null int64\n",
      "ARR_TIME               295832 non-null float64\n",
      "ARR_DELAY              295832 non-null float64\n",
      "CRS_ELAPSED_TIME       295832 non-null int64\n",
      "ACTUAL_ELAPSED_TIME    295832 non-null float64\n",
      "AIR_TIME               295832 non-null float64\n",
      "DISTANCE               295832 non-null int64\n",
      "dtypes: float64(6), int64(5), object(5)\n",
      "memory usage: 32.7+ MB\n"
     ]
    }
   ],
   "source": [
    "delays_df.dropna(inplace=True)\n",
    "delays_df.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "When data is loaded from multiple data sources you sometimes end up with duplicate records. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seattle-Tacoma</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dulles</td>\n",
       "      <td>Washington</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Dulles</td>\n",
       "      <td>Washington</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Heathrow</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Schiphol</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Name        City         Country\n",
       "0  Seattle-Tacoma     Seattle             USA\n",
       "1          Dulles  Washington             USA\n",
       "2          Dulles  Washington             USA\n",
       "3        Heathrow      London  United Kingdom\n",
       "4        Schiphol   Amsterdam     Netherlands"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airports_df = pd.read_csv('Data/airportsDuplicateRows.csv')\n",
    "airports_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "use **duplicates** to find the duplicate rows.\n",
    "\n",
    "If a row is a duplicate of a previous row it returns **True**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3    False\n",
       "4    False\n",
       "5    False\n",
       "6    False\n",
       "7    False\n",
       "dtype: bool"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airports_df.duplicated()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**drop_duplicates** will delete the duplicate rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Name</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Seattle-Tacoma</td>\n",
       "      <td>Seattle</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Dulles</td>\n",
       "      <td>Washington</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Heathrow</td>\n",
       "      <td>London</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Schiphol</td>\n",
       "      <td>Amsterdam</td>\n",
       "      <td>Netherlands</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Changi</td>\n",
       "      <td>Singapore</td>\n",
       "      <td>Singapore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Pearson</td>\n",
       "      <td>Toronto</td>\n",
       "      <td>Canada</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Narita</td>\n",
       "      <td>Tokyo</td>\n",
       "      <td>Japan</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             Name        City         Country\n",
       "0  Seattle-Tacoma     Seattle             USA\n",
       "1          Dulles  Washington             USA\n",
       "3        Heathrow      London  United Kingdom\n",
       "4        Schiphol   Amsterdam     Netherlands\n",
       "5          Changi   Singapore       Singapore\n",
       "6         Pearson     Toronto          Canada\n",
       "7          Narita       Tokyo           Japan"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "airports_df.drop_duplicates(inplace=True)\n",
    "airports_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
